#!/usr/bin/perl
#
# Author: Gary Locke
#
# Description:
#   Search for and remove IPs on blazingbucks' mycontentengine.com databases.
#
# To do:
#   Add limit to DELETE query based on number of results from SELECT.
#
# Usage:
#   ./script.pl <IP>

use strict;

use DBI;
use File::Find;

my $mysql_user = "root";
my $mysql_dir = "/var/lib/mysql/";
my $hex;
my $db_conn;

my @dbs;

# Get IP.
my $ip = @ARGV[0];
if ( ! defined($ip)) {
    print "No argument given. Enter IP address to remove: ";
    $ip = <STDIN>;
    chomp($ip);
}

# Break IP into octets and concat hex for each into $hex.
my @ip_arr = split(/\./, $ip);
foreach (@ip_arr) {
    $hex = $hex . sprintf("%02x", $_);
}

# Get decimal value for $hex.
my $dec = hex("$hex");

# Get password.
print "Enter MySQL password for '" . $mysql_user . "': ";
my $mysql_pass = <STDIN>;
chomp($mysql_pass);

# Push databases into array.
find(\&find_sub, $mysql_dir);
sub find_sub {
    if ($_ =~ /.*geoBlock.[Ff][Rr][Mm]$/) {
        my @db_path = split(/\//, $File::Find::dir);
        push(@dbs, @db_path[-1]);
    }
}

# Loop through databases.
foreach (@dbs) {

    # Connect to db.
    $db_conn = DBI->connect("DBI:mysql:$_", "$mysql_user", "$mysql_pass"
                     ) || die "Could not connect to database: $DBI::errstr";

    print "\nChecking " . $_ . " database for ranges containing " . $dec . ".\n";

    # Check geoBlock table for range containing $dec.
    my $stmt = $db_conn->prepare("select * from geoBlock where startIpNum <= '" . $dec . "' AND endIpNum >= '" . $dec . "';");
    $stmt->execute();
    my $result = $stmt->fetchrow_hashref();
    $stmt->finish;

    # Print results.
    if ($result->{startIpNum}) {

        # This should print all results!
        print "Value returned: $result->{startIpNum}\n";

        # Confirm removal.
        print "\n\nRemove? [y/N]: ";
        my $cont = <STDIN>;

        if ($cont !~ /[yY]/) {
            $db_conn->disconnect();
            die "\nQuitting...\n";
        }

        # Delete from db.
        my $del = $db_conn->do("delete from geoBlock where startIpNum <= '" . $dec . "' AND endIpNum >= '" . $dec . "';");

        # Print results.
        print "Deleted $del rows.\n";

        # Close SQL connection.
        $db_conn->disconnect();

    } else {

        print "No results found.\n";

        # Close SQL connection.
        $db_conn->disconnect();

    }

}

# Also check hit_db database (invalidLogin table) for IP.
print "\nChecking 'hit_db' database for " . $ip . ".\n";

$db_conn = DBI->connect('DBI:mysql:hit_db', "$mysql_user", "$mysql_pass"
                 ) || die "Could not connect to database: $DBI::errstr";

my $stmt = $db_conn->prepare("select ip from invalidLogin where ip = '" . $ip . "';");
$stmt->execute();
my $result = $stmt->fetchrow_hashref();
$stmt->finish;

# Print results.
if ($result->{ip}) {

    print "Value returned: $result->{ip}\n";

    # Confirm removal.
    print "\n\nRemove? [y/N]: ";
    my $cont = <STDIN>;

    if ($cont !~ /[yY]/) {
        $db_conn->disconnect();
        die "\nQuitting...\n";
    }

    # Delete from db.
    my $del = $db_conn->do("delete from invalidLogin where ip = '" . $ip . "';");

    # Close SQL connection
    $db_conn->disconnect();

    die "Deleted $del rows.\n";

} else {

    # Close SQL connection
    $db_conn->disconnect();

    die "No results found.\n\n";

}
